package com.sxs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.sxs.model.Per_user;
import com.sxs.model.Per_user_education;
import com.sxs.model.Per_user_note;
import com.sxs.model.Per_user_vacancies;

@Repository
// 持久层
public class PersonalAddDao implements PersonalAddDaoInterface {
	Logger logger = Logger.getLogger(PersonalAddDao.class);
	@Autowired
	private JdbcTemplate jdbcTemplatePer;

	@Override
	public int getAddUser(Per_user perUser) {
		String sql = "INSERT INTO per_user (userName, deptId,deptName, roleId, staffNo, workNo, idCard, "
				+ "nativePlace, nation, maritalStatus, accountType, accountLocation, pictureUrl) VALUES"
				+ "(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = { perUser.getUserName(), perUser.getDeptId(), perUser.getDeptName(), perUser.getRoleId(),
				perUser.getStaffNo(), perUser.getWorkNo(), perUser.getIdCard(), perUser.getNativePlace(),
				perUser.getNation(), perUser.getMaritalStatus(), perUser.getAccountType(), perUser.getAccountLocation(),
				perUser.getPictureUrl() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
		int autoId = keyHolder.getKey().intValue();
		return autoId;
	}

	@Override
	public int getAddUserVacancies(Per_user_vacancies puv) {
		String sql = "INSERT INTO per_user_vacancies (userId, typeOfWork, administrativeLevel, employeeType,"
				+ " position, theTitle, inTheTime, titleLevel, jobs, workingState, inWorkTime, telNo, "
				+ "emailNo, staffQQ, address) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = { puv.getUserId(), puv.getTypeOfWork(), puv.getAdministrativeLevel(), puv.getEmployeeType(),
				puv.getPosition(), puv.getTheTitle(), puv.getInTheTime(), puv.getTitleLevel(), puv.getJobs(),
				puv.getWorkingState(), puv.getInWorkTime(), puv.getTelNo(), puv.getEmailNo(), puv.getStaffQQ(),
				puv.getAddress() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
		int autoId = keyHolder.getKey().intValue();
		return autoId;
	}

	@Override
	public int getAddUserEducation(Per_user_education pue) {
		String sql = "INSERT INTO per_user_education ( userId, staffHtghestSchool, "
				+ "staffHtghestDegree, graduationDate, graduationSchool, staffMajor, "
				+ "computerMajor, foreignLanguage1, foreignLevel1, foreignLanguage2,"
				+ " foreignLevel2, foreignLanguage3, foreignLevel3, staffSkills) VALUES "
				+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = { pue.getUserId(), pue.getStaffHtghestSchool(), pue.getStaffHtghestDegree(),
				pue.getGraduationDate(), pue.getGraduationSchool(), pue.getStaffMajor(), pue.getComputerMajor(),
				pue.getForeignLanguage1(), pue.getForeignLevel1(), pue.getForeignLanguage2(), pue.getForeignLevel2(),
				pue.getForeignLanguage3(), pue.getForeignLevel3(), pue.getStaffSkills() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
		int autoId = keyHolder.getKey().intValue();
		return autoId;
	}

	@Override
	public int getAddUserNote(Per_user_note pun) {
		String sql = "INSERT INTO per_user_note ( userId, note) VALUES(?,?)";
		Object[] params = { pun.getUserId(), pun.getNote() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
		int autoId = keyHolder.getKey().intValue();
		return autoId;
	}

	@Override
	public Map<String, Object> getSelectUser(String userId) {
		String sql = "select * from per_user as pu INNER JOIN per_user_education as pue "
				+ "ON pu.id = pue.userId INNER JOIN per_user_note as pun ON pu.id = pun.userId "
				+ "INNER JOIN per_user_vacancies as puv on pu.id = puv.userId " + "where pu.id = '" + userId + "' and isDelete=2";
		Map<String, Object> map = jdbcTemplatePer.queryForMap(sql);
		return map;
	}

	@Override
	public void getUpdateUser(Per_user perUser) {
		String sql = "UPDATE per_user SET userName=?, deptId=?, deptName=?, roleId=?, staffNo=?, workNo=?, "
				+ "idCard=?, nativePlace=?, nation=?, maritalStatus=?, accountType=?, accountLocation=?, pictureUrl=? WHERE (id=?)";

		Object[] params = { perUser.getUserName(), perUser.getDeptId(), perUser.getDeptName(), perUser.getRoleId(),
				perUser.getStaffNo(), perUser.getWorkNo(), perUser.getIdCard(), perUser.getNativePlace(),
				perUser.getNation(), perUser.getMaritalStatus(), perUser.getAccountType(), perUser.getAccountLocation(),
				perUser.getPictureUrl(), perUser.getId() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
	}

	@Override
	public void getUpdateUserVacancies(Per_user_vacancies puv) {
		String sql = "UPDATE per_user_vacancies SET typeOfWork=?, administrativeLevel=?, employeeType=?, position=?, theTitle=?,"
				+ " inTheTime=?, titleLevel=?, jobs=?, workingState=?, inWorkTime=?, telNo=?, emailNo=?, staffQQ=?, address=? WHERE (userId=?);";
		Object[] params = {  puv.getTypeOfWork(), puv.getAdministrativeLevel(), puv.getEmployeeType(),
				puv.getPosition(), puv.getTheTitle(), puv.getInTheTime(), puv.getTitleLevel(), puv.getJobs(),
				puv.getWorkingState(), puv.getInWorkTime(), puv.getTelNo(), puv.getEmailNo(), puv.getStaffQQ(),
				puv.getAddress(),puv.getUserId() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
	}

	@Override
	public void getupdateUserEducation(Per_user_education pue) {
		String sql = "UPDATE per_user_education SET staffHtghestSchool=?, staffHtghestDegree=?, graduationDate=?, "
				+ "graduationSchool=?, staffMajor=?, computerMajor=?, foreignLanguage1=?, foreignLevel1=?, "
				+ "foreignLanguage2=?, foreignLevel2=?, foreignLanguage3=?, foreignLevel3=?, staffSkills=? WHERE (userId=?);";
		Object[] params = {  pue.getStaffHtghestSchool(), pue.getStaffHtghestDegree(),
				pue.getGraduationDate(), pue.getGraduationSchool(), pue.getStaffMajor(), pue.getComputerMajor(),
				pue.getForeignLanguage1(), pue.getForeignLevel1(), pue.getForeignLanguage2(), pue.getForeignLevel2(),
				pue.getForeignLanguage3(), pue.getForeignLevel3(), pue.getStaffSkills(),pue.getUserId() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
	}

	@Override
	public void getupdateUserNote(Per_user_note pun) {
		String sql = "UPDATE per_user_note SET note=? WHERE (userId=?)";
		Object[] params = { pun.getNote(), pun.getUserId() };
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplatePer.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
	}

	@Override
	public List<Map<String, Object>> getSelectListUser(String deptId) {
		String sql = "select id,userName,idCard,nativePlace,nation,deptId from per_user where (deptId = '"+deptId+"' or deptId='0') and isDelete=2 ";
		List<Map<String, Object>> list = jdbcTemplatePer.queryForList(sql);
		return list;
	}

	@Override
	public void getDeleteUser(String userId) {
		String sql = "update per_user set isDelete=1 where id='"+userId+"'";
		jdbcTemplatePer.update(sql);
	}

}
